package ar.com.vates.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Conexion {

        private static final String nombreBD = "practico6";
        private static final String user = "root";
        private static final String pass = "roycarrion";
        private static final String server = "jdbc:mysql://localhost:3306/"
                        + nombreBD;

        private static Connection conn;

        public Conexion() {
        }

        private static void conectar() throws ClassNotFoundException, SQLException {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(server, user, pass);
                // System.out.println("Conexion exitosa");
        }

        public static int alta(Alumno a) throws SQLException {
                int res = -1;
                PreparedStatement ps = null;
                try {

                        conectar();

                        if (conn != null && verificarEmail(a.getEmail()) == 0) {
                                ps = conn
                                                .prepareStatement(
                                                                "INSERT INTO Alumnos (email, clave, nombre, apellidos, edad, calle, numero, codigo) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                                                                PreparedStatement.RETURN_GENERATED_KEYS);

                                ps.setString(1, a.getEmail());
                                ps.setString(2, a.getClave());
                                ps.setString(3, a.getNombre());
                                ps.setString(4, a.getApellidos());
                                ps.setInt(5, a.getEdad());
                                ps.setString(6, a.getCalle());
                                ps.setInt(7, a.getNumero());
                                ps.setString(8, a.getCodigo());

                                int aux = ps.executeUpdate();

                                // a continuacion obtengo el id generado para poder mostrarlo
                                if (aux != 0) {

                                        ResultSet rs = ps.getGeneratedKeys();
                                        while (rs.next()) {
                                                res = rs.getInt(1);
                                        }
                                }
                        }
                } catch (ClassNotFoundException e) {
                        System.out.println("Error: " + e.getMessage());
                } finally {
                        desconectar(ps);
                }
                return res;
        }

        public static boolean baja(String email) throws SQLException {
                Statement st = null;
                try {

                        conectar();

                        if (conn != null && verificarEmail(email) != 0) {
                                st = conn.createStatement();
                                int res = st.executeUpdate("DELETE FROM Alumnos WHERE email='"
                                                + email + "'");

                                if (res != 0)
                                        return true;
                        }

                } catch (ClassNotFoundException e) {
                        System.out.println("Error: " + e.getMessage());
                } finally {
                        desconectar(st);
                }
                return false;
        }

        public static String listar() throws SQLException {
                String resultado = "No hay ningun alumno";
                Statement st = null;
                ResultSet rs = null;
                ResultSetMetaData metaData = null;

                try {

                        conectar();

                        if (conn != null) {
                                st = conn.createStatement();
                                rs = st.executeQuery("SELECT nombre AS Nombre, apellidos AS Apellidos, "
                                                + "email AS Email, edad AS Edad, calle AS Calle, numero AS Numero, "
                                                + "codigo AS CP FROM Alumnos;");
                        }

                        StringBuffer aux = new StringBuffer();

                        if (rs != null) {

                                metaData = (ResultSetMetaData) rs.getMetaData();
                                int cantidadColumnas = metaData.getColumnCount();

                                for (int i = 1; i <= cantidadColumnas; i++) {
                                        aux.append(metaData.getColumnLabel(i) + "\t\t");
                                }
                                aux.append("\n");

                                while (rs.next()) {
                                        for (int i = 1; i <= cantidadColumnas; i++) {
                                                aux.append(rs.getObject(i) + "\t\t");
                                        }
                                        aux.append("\n");
                                }
                        }
                        resultado = new String(aux);
                } catch (ClassNotFoundException e) {
                        System.out.println("Error: " + e.getMessage());
                } finally {
                        desconectar(rs, st, true);
                }
                return resultado;
        }

        private static void desconectar(ResultSet rs, Statement st, boolean conexion)
                        throws SQLException {
                
                if (conn != null && conexion == true)
                        conn.close();

                if (rs != null)
                        rs.close();

                if (st != null)
                        st.close();
        }

        private static void desconectar(PreparedStatement ps) throws SQLException {

                if (conn != null)
                        conn.close();

                if (ps != null)
                        ps.close();
        }

        private static void desconectar(Statement st) throws SQLException {
                
                if (conn != null)
                        conn.close();

                if (st != null)
                        st.close();
        }

        private static int verificarEmail(String email)
                        throws ClassNotFoundException, SQLException {
                // este metodo retorna la cantidad de alumnos con el mail pasado por
                // parametro
                Statement st = null;
                ResultSet rs = null;

                try {

                        if (conn != null) {
                                st = conn.createStatement();
                                rs = st.executeQuery("SELECT COUNT(*) FROM Alumnos WHERE email='"
                                                + email + "'");

                                int cant = -1;

                                while (rs.next()) {
                                        cant = rs.getInt(1);
                                }

                                if (cant != 0)
                                        return cant;
                        }
                } catch (SQLException e) {
                        System.out.println("Error: " + e.getMessage());
                } finally {
                        desconectar(rs, st, false);
                }
                return 0;
        }
}
